﻿//Copyrigh (C) 2021 上海东欣软件工程有限公司 版权所有。
//<author>邢耀光</author>
//<data>2021.1.31</data>

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace Dal
{
    /// <summary>
    /// 数据库操作帮助类
    /// </summary>
    public static class Dbutil
    {
        /// <summary>
        /// 数据库增删改帮助类
        /// </summary>
        /// <param name="sql">数据库操作语句</param>
        /// <param name="connString">数据库连接字符串</param>
        /// <returns>操作影响结果，1成功，0失败</returns>
        public static int ExecuteNonQuery(string sql, string connString)
        {
            try
            {
                int result = 0;
                using (SqlConnection conn = new SqlConnection(connString))
                {
                    conn.Open();
                    using (SqlCommand cmd = new SqlCommand(sql, conn))
                    {
                        result = cmd.ExecuteNonQuery();
                    }
                    conn.Close();
                    return result;
                }
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }            
        }

        /// <summary>
        /// 数据库查询帮助类
        /// </summary>
        /// <param name="sql">数据库操作语句</param>
        /// <param name="connString">数据库连接字符串</param>
        /// <returns>查询结果</returns>
        public static IDataReader ExecuteReader(string sql, string connString)
        {
            try
            {
                SqlConnection conn = new SqlConnection(connString);
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    IDataReader result = cmd.ExecuteReader();
                    return result;
                }
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }            
        }

        /// <summary>
        /// 数据库查询帮助类
        /// </summary>
        /// <param name="sql">数据库操作语句</param>
        /// <param name="connString">数据库连接字符串</param>
        /// <returns>查询结果</returns>
        public static DataTable ExecuteAdapter(string sql, string connString)
        {
            try
            {
                SqlConnection conn = new SqlConnection(connString);
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    SqlDataAdapter adapter = new SqlDataAdapter();
                    adapter.SelectCommand = cmd;
                    DataTable dataTable = new DataTable();
                    adapter.Fill(dataTable);
                    return dataTable;
                }
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
        }
        /// <summary>
        /// 数据库查询帮助类
        /// </summary>
        /// <param name="sql">数据库操作语句</param>
        /// <param name="connString">数据库连接字符串</param>
        /// <returns>查询结果</returns>
        public static T ExecuteScalar<T>(string sql, string connString)
        {
            try
            {
                SqlConnection conn = new SqlConnection(connString);
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    T result = (T)cmd.ExecuteScalar();
                    return result;
                }
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }            
        }
   

        /// <summary>
        /// 获取受影响的行数,不带参数，执行sql语句
        /// </summary>
        /// <returns></returns>
        //public static int ExecuteNonQuery(string sql, string connString)
        //{
        //    return ExecuteNonQuery(sql, CommandType.Text, connString,null);
        //}

        /// <summary>
        /// 获取受影响的行数，带参数，选择执行的是存储过程还是普通的sql语句
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="ps"></param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string sql, CommandType type, string connString,params SqlParameter[] ps)
        {
            int rows = -1;

            using (SqlConnection conn = new SqlConnection(connString))
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                //定义执行语句的类型
                cmd.CommandType = type;
                if (ps != null)
                    cmd.Parameters.AddRange(ps);
                conn.Open();
                rows = cmd.ExecuteNonQuery();
            }
            return rows;
        }

        /// <summary>
        /// 获取首行首列的值
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="ps"></param>
        /// <returns></returns>
        public static object ExecuteScalar(string sql, string connString, params SqlParameter[] ps)
        {
            object obj = null;

            using (SqlConnection conn = new SqlConnection(connString))
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                if (ps != null)
                    cmd.Parameters.AddRange(ps);
                conn.Open();
                obj = cmd.ExecuteScalar();
            }
            return obj;
        }

        /// <summary>
        /// 获取一个结果集
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="connString">连接字符串</param>
        /// <param name="dic">字典</param>
        /// <returns></returns>
        public static DataTable GetDataTable(string sql, string connString, Dictionary<string, string> dic)
        {
            DataTable dt = new DataTable();

            using (SqlConnection conn = new SqlConnection(connString))
            {
                SqlDataAdapter adapter = new SqlDataAdapter(sql, conn);
                if (dic != null)
                {
                    SqlParameter[] ps = new SqlParameter[dic.Count];
                    int index = 0;
                    foreach (var item in dic)
                    {
                        ps[index++] = new SqlParameter(item.Key, item.Value);
                    }
                    adapter.SelectCommand.Parameters.AddRange(ps);
                }
                conn.Open();
                adapter.Fill(dt);
            }
            return dt;
        }
    }
}
